import openpyxl as xl

COLUMN_NAME_PROVINCE = "PROVINCE"
COLUMN_NAME_CITY = "CITY"
COLUMN_NAME_ADDRESS = "ADDRESS"


def main():
    work_book = xl.load_workbook('WorkBook.xlsx')
    sheet_names = work_book.sheetnames
    sheet = work_book[sheet_names[0]]

    row_list = list(sheet.rows)
    header_row = row_list[0]
    column_name_dict = {cell.value: cell.column for cell in header_row}

    for i in range(1, 50):
        row = row_list[i]
        province_cell = row[column_name_dict.get(COLUMN_NAME_PROVINCE) - 1]
        city_cell = row[column_name_dict.get(COLUMN_NAME_CITY) - 1]
        address_cell = row[column_name_dict.get(COLUMN_NAME_ADDRESS) - 1]

        province = province_cell.value
        city = city_cell.value
        if (not province or province.isspace()) and (not city or city.isspace()):
            print("Row {} is skipped without province and city value".format(i + 1))
            continue

        address = address_cell.value
        if address and not address.isspace():
            print("Row {} is skipped because address exists".format(i + 1))
            continue

        address_cell.value = province + city

    work_book.save('WorkBook-new.xlsx')


if __name__ == '__main__':
    main()
